# -*- coding: utf-8 -*-

"""
PyoCDVD (http://code.google.com/p/pyocdvd/)
Media Organizer (CD, DVD, etc)

Developers:
    Bruno Pitteli Gonçalves <scorninpc(*)gmail(*)com>
    Paul Eipper <lkraider(*)gmail(*)com>

Licence: GNU GENERAL PUBLIC LICENSE v3

"""

from pysqlite2 import dbapi2 as sqlite

class Database_SQL(object):
    '''Low level functions abstraction for the database.'''
    
    def __init__(self, database_file):
        self.database_file = database_file
        self.connect()
    
    def connect(self):
        '''Open the database connection.'''
        self.connection = sqlite.connect(self.database_file)
        self.cursor = self.connection.cursor()
        
    def create_database(self, filename):
        '''Load and execute the database creation script.'''
        sql = open(filename, 'r').read()
        self.connection.executescript(sql)
        
    def commit(self):
        self.connection.commit()

    def insert(self, table, data={}):
        '''Insert the dictionary data into a table.
        
        table is the table to insert into;
        data is a dictionary with the database fields and the values to insert.
        
        '''
        fields = ', '.join([str(key) for key in data.keys()])
        values = ', '.join(['?' for value in data.values()])
        sql_string = 'INSERT INTO %s (%s) VALUES (%s)' % (table, fields, values)
        self.cursor.execute(sql_string, data.values())

    def next_id(self, table, id_field):
        '''Return the next usable id for a table.'''
        self.select(table, 'MAX(%s)' % id_field)
        next_id = self.cursor.fetchone()[0]
        if next_id is None:
            return 1
        else:
            return next_id + 1
            
    def select(self, table, fields):
        '''Select fields from a table.
        
        fields is a list or string with the table fields to select.
        
        '''
        if type(fields) == list:
            fields = ', '.join([str(field) for field in fields])
        sql_string = 'SELECT %s FROM %s' % (fields, table)
        return self.cursor.execute(sql_string)

    def select_where(self, table, fields, condition={}, compare_like=False):
        '''Select fields from a table based on conditionals.
        
        fields is a list or string with the table fields to select;
        condition is a dict of the comparisons to be made (key: value -> field = 'value')
            enclose the value in a list if you want to compare fields (key: [value] -> field = field)
        compare_like is a boolean on whether to use SQL LIKE comparison on the string fields.
        
        '''
        if type(fields) == list:
            fields = ', '.join([str(field) for field in fields])
        values = []
        sql_where = []
        for key, value in condition.items():
            if type(value) == list:
                sql_where.append('%s = %s' % (key, value[0]))
            else:
                if compare_like:
                    sql_where.append('%s LIKE ?' % key)
                    values.append('%' + value + '%')
                else:
                    sql_where.append('%s = ?' % key)
                    values.append(value)
        sql_where = ' AND '.join([str(cond) for cond in sql_where])
        sql_string = 'SELECT %s FROM %s WHERE %s' % (fields, table, sql_where)
        return self.cursor.execute(sql_string, values)
        
    def close(self):
        '''Close the database connection.'''
        self.connection.close()
        
    def __del__(self):
        '''Close the database connection on object destruction.'''
        self.close()